using System;
using System.Collections.Generic;
using System.Text;
using Fieldbook.Entities;
using Microsoft.Practices.EnterpriseLibrary.Data.Sql;
using System.Data.Common;
using System.Data;
using Fieldbook.Common;

namespace Fieldbook.DAL
{
    public static class MachineDAO
    {
        #region insert
        private static DbCommand GetInsertCommand(SqlDatabase db)
        {
            string insertSQL = @"INSERT INTO dbo.Machine (CATEGORYID, REGISTRATION, HOUSEHOLDID, DESCRIPTION, POWER, POWERMU, VOLUMEN, VOLUMENMU, PRODUCTIONYEAR) 
                    VALUES (@CATEGORYID, @REGISTRATION, @HOUSEHOLDID, @DESCRIPTION, @POWER, @POWERMU, @VOLUMEN, @VOLUMENMU, @PRODUCTIONYEAR) SET @MACHINEID = SCOPE_IDENTITY()";
            DbCommand insertCommand = db.GetSqlStringCommand(insertSQL);
            db.AddOutParameter(insertCommand, "@MACHINEID", DbType.Int64, 8);
            db.AddInParameter(insertCommand, "@CATEGORYID", DbType.Int64);
            db.AddInParameter(insertCommand, "@REGISTRATION", DbType.String);
            db.AddInParameter(insertCommand, "@HOUSEHOLDID", DbType.Int64);
            db.AddInParameter(insertCommand, "@DESCRIPTION", DbType.String);
            db.AddInParameter(insertCommand, "@POWER", DbType.Decimal);
            db.AddInParameter(insertCommand, "@POWERMU", DbType.Int32);
            db.AddInParameter(insertCommand, "@VOLUMEN", DbType.Decimal);
            db.AddInParameter(insertCommand, "@VOLUMENMU", DbType.Int32);
            db.AddInParameter(insertCommand, "@PRODUCTIONYEAR", DbType.Int32);
            return insertCommand;
        }
        private static void SetInsertValues(SqlDatabase db, DbCommand cmd, Machine entity)
        {
            db.SetParameterValue(cmd, "@CATEGORYID", entity.CategoryID);
            db.SetParameterValue(cmd, "@REGISTRATION", entity.Registration);
            db.SetParameterValue(cmd, "@HOUSEHOLDID", entity.HouseHoldID);
            db.SetParameterValue(cmd, "@DESCRIPTION", entity.Description);
            db.SetParameterValue(cmd, "@POWER", entity.Power);
            db.SetParameterValue(cmd, "@POWERMU", entity.PowerMU);
            db.SetParameterValue(cmd, "@VOLUMEN", entity.Volumen);
            db.SetParameterValue(cmd, "@VOLUMENMU", entity.VolumenMU);
            db.SetParameterValue(cmd, "@PRODUCTIONYEAR", entity.ProductionYear);
        }
        public static bool InsertMachine(Machine entity)
        {
            bool res = false;
            SqlDatabase db = new SqlDatabase(AppSettingsHelper.GetConfigValue(Constants.FieldbookConnectionString));
            DbCommand cmd = GetInsertCommand(db);
            SetInsertValues(db, cmd, entity);
            res = Convert.ToBoolean(db.ExecuteNonQuery(cmd));
            if (res)
            {
                entity.MachineID = (long?)db.GetParameterValue(cmd, "@MACHINEID");
            }
            return res;
        }
        #endregion

        #region select
        private static List<Machine> LoadMachines(SqlDatabase db, DbCommand command)
        {
            List<Machine> res = new List<Machine>();
            IDataReader reader = null;
            try
            {
                reader = db.ExecuteReader(command);
                if (reader != null)
                {
                    while (reader.Read())
                    {
                        Machine entity = new Machine();
                        entity.MachineID = reader.GetInt64(0);
                        entity.CategoryID = reader.GetInt64(1);
                        entity.Registration = reader.GetString(2);
                        entity.HouseHoldID = reader.GetInt64(3);
                        entity.Description = reader.IsDBNull(4) ? null : reader.GetString(4);
                        entity.Power = reader.IsDBNull(5) ? null : (decimal?)reader.GetDecimal(5);
                        entity.PowerMU = reader.IsDBNull(6) ? null : (int?)reader.GetInt32(6);
                        entity.Volumen = reader.IsDBNull(7) ? null : (decimal?)reader.GetDecimal(7);
                        entity.VolumenMU = reader.IsDBNull(8) ? null : (int?)reader.GetInt32(8);
                        entity.ProductionYear = reader.IsDBNull(9) ? null : (int?)reader.GetInt32(9);
                        res.Add(entity);
                    }
                    reader.Close();
                }
            }
            catch (Exception ex)
            {
                if (reader != null && !reader.IsClosed)
                {
                    reader.Close();
                }
                res = null;
                throw ex;
            }
            return res;
        }

        public static List<Machine> LoadMachines(long userID)
        {
            string selectSQL = @"SELECT M.MACHINEID, M.CATEGORYID, M.REGISTRATION, M.HOUSEHOLDID, M.DESCRIPTION, M.POWER, M.POWERMU, M.VOLUMEN, M.VOLUMENMU, M.PRODUCTIONYEAR
FROM MachineCategory MC 
LEFT OUTER JOIN HouseHold HH ON MC.HOUSEHOLDID = HH.HEADID OR MC.HOUSEHOLDID IS NULL
JOIN Machine M ON MC.CATEGORYID = M.CATEGORYID 
WHERE HH.MEMBERID = @MEMBERID";
            SqlDatabase db = new SqlDatabase(AppSettingsHelper.GetConfigValue(Constants.FieldbookConnectionString));
            DbCommand cmd = db.GetSqlStringCommand(selectSQL);
            db.AddInParameter(cmd, "@MEMBERID", DbType.Int64, userID);
            return LoadMachines(db, cmd);
        }

        public static List<Machine> LoadMachines(long userID, long categoryID)
        {
            string selectSQL = @"SELECT M.MACHINEID, M.CATEGORYID, M.REGISTRATION, M.HOUSEHOLDID, M.DESCRIPTION, M.POWER, M.POWERMU, M.VOLUMEN, M.VOLUMENMU, M.PRODUCTIONYEAR
FROM MachineCategory MC 
LEFT OUTER JOIN HouseHold HH ON MC.HOUSEHOLDID = HH.HEADID OR MC.HOUSEHOLDID IS NULL
JOIN Machine M ON MC.CATEGORYID = M.CATEGORYID 
WHERE HH.MEMBERID = @MEMBERID AND M.CATEGORYID = @CATEGORYID";
            SqlDatabase db = new SqlDatabase(AppSettingsHelper.GetConfigValue(Constants.FieldbookConnectionString));
            DbCommand cmd = db.GetSqlStringCommand(selectSQL);
            db.AddInParameter(cmd, "@MEMBERID", DbType.Int64, userID);
            db.AddInParameter(cmd, "@CATEGORYID", DbType.Int64, categoryID);
            return LoadMachines(db, cmd);
        }
        #endregion
    }
}
